package forminrecipe.core;
	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.Statement;
	import java.sql.DriverManager;
	import java.sql.SQLException;
	import forminrecipe.data.*;
	
public class dbManager {
	
	private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;
	private Core coreRef = null;
	private SQLSettings sqlSettings = null;
	
	//constructor - generates a references to the core and gets the sql server settings
	public dbManager(Core coreRef){
		this.coreRef=coreRef;
	}
	
	//connects to mySQLServer Database
	public boolean connectToDatabase(SQLSettings sqlSet){
		sqlSettings = sqlSet;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://"+sqlSettings.sqlAddress, sqlSettings.sqlUserName, sqlSettings.sqlPassword);
			statement = connect.createStatement();
		}catch (Exception e){
			System.out.println("DBM: Error Connecting To mySQLServer");
			System.out.println(e.getMessage());
			return false;
		}	
		try {
			statement.execute("use "+sqlSettings.sqlDbName);
		} catch (SQLException e) {
			System.out.println("DBM: Error Using Database:"+sqlSettings.sqlDbName);
			System.out.println(e.getMessage());
			
			//todo - decision popup
			createDatabases();
			return false;
		}
		return true;
	}
	private void reConnectToDatabase(){

	}
	
	// read from database functions - catagories, recipelist, list, recipe# & ingrendients#
	public boolean readCatagoryDataBase() {
		try {
			resultSet = statement.executeQuery("select * from "+constants.DATABASE_TABLE_CATAGORIES);
			coreRef.addCatagoryGUI("-");
			while(resultSet.next()){
				coreRef.addCatagoryGUI(resultSet.getString("catagory"));
			}				
		} catch (SQLException e) {
			
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
			return false;
		}
		return true;
	}
	
	public boolean readRecipeListDatabase(){
		RecipeData recipeData;
		try {
			resultSet = statement.executeQuery("select * from "+constants.DATABASE_TABLE_RECIPELIST);
			while(resultSet.next()){
				recipeData = new RecipeData(resultSet.getString(constants.DATABASE_FEILD_NAME), 
						resultSet.getString(constants.DATABASE_FEILD_TIME),
						resultSet.getInt(constants.DATABASE_FEILD_RATING), 
						resultSet.getInt(constants.DATABASE_FEILD_DIFFICULTY), 
						resultSet.getString(constants.DATABASE_FEILD_PROCESS), 
						resultSet.getInt(constants.DATABASE_FEILD_ID),
						resultSet.getString(constants.DATABASE_FEILD_CATAGORY));
				coreRef.addRecipeListGUI(recipeData);
			}				
		} catch (SQLException e) {
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
			return false;
		}
		return true;
	}
	
	public boolean readListDatabase(){
		try {
			resultSet = statement.executeQuery("select * from "+constants.DATABASE_TABLE_LIST);
			while(resultSet.next()){
				coreRef.addListGUI(resultSet.getString(constants.DATABASE_FEILD_ITEM));
			}				
		} catch (SQLException e) {
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
			return false;
		}
		return true;
	}
	
	public void getIngredientsForDMB(int id){		
		//read in the ingredients
		try {
			resultSet = statement.executeQuery("select * from "+constants.DATABASE_TABLE_INGREDIENTS+id);
			while(resultSet.next()){
				//todo
				coreRef.addIngredientGUI(resultSet.getString(constants.DATABASE_FEILD_QUANTITY),
						constants.DATABASE_FEILD_UNITS, constants.DATABASE_FEILD_ITEM);
			}				
		} catch (SQLException e) {
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
			
			//todo - decision popup
			createIngredientsTable(id);
		}
	}
	
	// write to database functions
	public void addCatagoryDBM(String catagory){
		try{
			statement.execute("INSERT INTO "+constants.DATABASE_TABLE_CATAGORIES+"("+
					constants.DATABASE_FEILD_CATAGORY+") values('"+catagory+"')");
		}catch(SQLException e){
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
			createListTable();
		}
	}
	
	public void addListItemDBM(String item){
		try{
			statement.execute("INSERT INTO "+constants.DATABASE_TABLE_LIST+"("+constants.DATABASE_FEILD_ITEM+") values('"+item+"')");
		}catch(SQLException e){
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
	}
		
	public void saveRecipeDBM(String name, int rating, int difficulty, String time, String catagory, String process ){
		try{
			statement.execute("INSERT INTO recipelist (name, rating, difficulty, time, catagory, process) " +
					"values('"+name+"',"+rating+","+difficulty+",'"+time+"','"+catagory+"','"+process+"')");
		}catch(SQLException e){
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
	}

	// creation functions for all the tables and databases on the mySQLServer
	private boolean createDatabases(){
		System.out.println("DBM: Creating New Databases :"+sqlSettings.sqlDbName);
		try{
			statement.execute("create database "+sqlSettings.sqlDbName);
		}catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		//retry connecting after creating database
		if (sqlSettings != null){
			return connectToDatabase(sqlSettings);
		}
		return false;
	}
	
	private void createRecipeListTable(){
		System.out.println("DBM: Creating New Recipe Table :");
		try{
			statement.execute("create table recipelist( id int primary key NOT NULL auto_increment," +
					"name varchar(30), rating int, difficulty int, " +
					"time varchar(8),catagory varchar(30), " +
					"process text)");
		}catch(SQLException e){
			System.out.println(e.getMessage());
		}
	}
	
	private void createListTable(){
		System.out.println("DBM: Creating New List Table :");
		try{
			statement.execute("create table list (id int primary key not NULL auto_increment, item varchar(30))");
		}catch(SQLException e){
			System.out.println(e.getMessage());
		}
	}
	
	private void createIngredientsTable(int id){
		System.out.println("DBM: Creating New Ingredients Table :"+id);
		try{
			statement.execute("create table ingredients"+id+"( id int primary key NOT NULL auto_increment, quantity varchar(8), units varchar(8), ingredient varchar(30))");
		}catch(SQLException e){
			System.out.println(e.getMessage());
		}
	}
}